package com.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * DBManager
 */
public class DBManager {

	private DAO dal;
	
	private static String SELECTFULLLISTSQL =  "select names from FULLLIST";
	private static String SELECTLISTSQL =  "select names from TEMPLIST";
	private static String SELECTREGDSQL =  "select NAME from REGUSERS";
	private static String DELETEREGUSER = "delete from TEMPLIST where NAMES = (?)";
	private static String UPDATEREGUSER = "insert into REGUSERS values (?,?)";
	private static String GETUSERFORSERIALSQL = "select names from FULLLIST where SERIALNO = (?)";
	private static String GETCUBEFORUSERSQL = "select CUBICLENO from FULLLIST where NAMES = (?)";
	private static String GETADMIRERFORGIFTER = "select GIFTEDNAME from REGUSERS where NAME = (?)";
	
	public DBManager() {
		dal = new DAO();
	}
	
	public List<String> getFullNameList() {
		return getSelectAllFor(SELECTFULLLISTSQL);
	}
	
	public List<String> getNameList() {
		return getSelectAllFor(SELECTLISTSQL);
	}
	
	public List<String> getRegisteredList() {
		return getSelectAllFor(SELECTREGDSQL);
	}
	
	
	public List<String> getSelectAllFor(String sql){
		List<String> list = new ArrayList<String>();
		Statement stmt = null;
		ResultSet result = null;
		try {
			stmt = dal.getConnection().createStatement();
			result = stmt.executeQuery(sql);
			while (result.next()) {
				list.add (result.getString(1));
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			if (result!=null)
				try {
					result.close();
				}
				catch (Exception e) {
					e.printStackTrace();
				}
			if (stmt!=null)
				try {
					stmt.close();
				}
				catch (Exception e) {
					e.printStackTrace();
				}
		}
		return list;
	}
	
	public boolean updateNameList(String name) {
		String sql = DELETEREGUSER;
		PreparedStatement stmt = null;

		try {
			stmt = dal.getConnection().prepareStatement(sql);
			stmt.setString(1, name);
			int result = stmt.executeUpdate();
			if (result == 1)
				return true;
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			if (stmt!=null)
				try {
					stmt.close();
				}
				catch (Exception e) {
					e.printStackTrace();
				}
		}
		return false;
	}
	
	public boolean updateRegisteredUser(String name, String giftTo) {
		String sql = UPDATEREGUSER;
		PreparedStatement stmt = null;
		try {
			stmt = dal.getConnection().prepareStatement(sql);
			stmt.setString(1, name);
			stmt.setString(2, giftTo);
			int result = stmt.executeUpdate();
			if (result == 1)
				return true;
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			if (stmt!=null)
				try {
					stmt.close();
				}
				catch (Exception e) {
					e.printStackTrace();
				}
		}
		return false;
	}
	
	public String getNameForSerial(String slNo) {
		PreparedStatement stmt = null;
		ResultSet result = null;
		try {
			stmt = dal.getConnection().prepareStatement(GETUSERFORSERIALSQL);
			stmt.setString(1, slNo);
			result = stmt.executeQuery();
			while (result.next()) {
				return result.getString(1);
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			if (result!=null)
				try {
					result.close();
				}
				catch (Exception e) {
					e.printStackTrace();
				}
			if (stmt!=null)
				try {
					stmt.close();
				}
				catch (Exception e) {
					e.printStackTrace();
				}
		}
		return new String("");
	}
	
	public Integer getCubicleNumberFor(String user) {
		PreparedStatement stmt = null;
		ResultSet result = null;
		try {
			stmt = dal.getConnection().prepareStatement(GETCUBEFORUSERSQL);
			stmt.setString(1, user);
			result = stmt.executeQuery();
			while (result.next()) {
				return result.getInt(1);
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			if (result!=null)
				try {
					result.close();
				}
				catch (Exception e) {
					e.printStackTrace();
				}
			if (stmt!=null)
				try {
					stmt.close();
				}
				catch (Exception e) {
					e.printStackTrace();
				}
		}
		return new Integer(0);
	}
	
	public String getAdmirerName(String gifter){
		PreparedStatement stmt = null;
		ResultSet result = null;
		try {
			stmt = dal.getConnection().prepareStatement(GETADMIRERFORGIFTER);
			stmt.setString(1, gifter);
			result = stmt.executeQuery();
			while (result.next()) {
				return result.getString(1);
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			if (result!=null)
				try {
					result.close();
				}
				catch (Exception e) {
					e.printStackTrace();
				}
			if (stmt!=null)
				try {
					stmt.close();
				}
				catch (Exception e) {
					e.printStackTrace();
				}
		}
		return "";
	}
	
}
